﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TrainingWorkshop.Models;

namespace TrainingWorkshop.DAL
{
    public class CourseRepository : ICourseRepository
    {
        private SqlConnection sqlconn;
        public CourseRepository(SqlConnection sqlconnection)
        {
            this.sqlconn = sqlconnection;
        }

        DataSet ds = new DataSet();
        public List<CourseShows> GetAllCourses()
        {

            string sql = "select CourseId ,CourseName,[Teacher].TeacherName,[Level].LevelName,CourseStatus from Course, Level,Teacher where [Course].TeacherId=[Teacher].TeacherId and [Course].LevelId=[Level].LevelId";
            List<CourseShows> courseshowlist = new List<CourseShows>();
            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlconn);
                //fill the sqldataadapter with the records selected from the database
                adapter.Fill(ds, "Course");
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    //add new object
                    Level myLevel = new Level();
                    Teacher myTeacher = new Teacher();
                    myLevel.LevelName = row[3] == null ? "" : row[3].ToString();
                    myTeacher.TeacherName = row[2] == null ? "" : row[2].ToString();
                    CourseShows courses = new CourseShows(myLevel, myTeacher);

                    courses.CourseId = Convert.ToInt32(row[0].ToString().Trim());
                    courses.CourseName = row[1].ToString().Trim();
                    courses.Teacher.TeacherName = row[2].ToString().Trim();
                    courses.Level.LevelName = row[3].ToString().Trim();
                    courses.CourseStatus = Convert.ToBoolean(row[4].ToString().Trim());
                    courseshowlist.Add(courses);
                }
                return courseshowlist;
            }
            finally
            {
                sqlconn.Close();
            }
        }

        public List<CourseShows> GetSelectedCourses(string name, bool status)
        {
            string sql = "select CourseId ,CourseName,[Teacher].TeacherName,[Level].LevelName,CourseStatus from Course, Level,Teacher where [Course].TeacherId=[Teacher].TeacherId and [Course].LevelId=[Level].LevelId and CourseName Like '%'+@name+'%' and CourseStatus=@status";
            List<CourseShows> courseshowlist = new List<CourseShows>();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, sqlconn);
                SqlParameter Sp = new SqlParameter("name", name);
                SqlParameter Sp1 = new SqlParameter("status", status);
                cmd.Parameters.Add(Sp);
                cmd.Parameters.Add(Sp1);
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Level myLevel = new Level();
                    Teacher myTeacher = new Teacher();
                    myLevel.LevelName = row[3] == null ? "" : row[3].ToString();
                    myTeacher.TeacherName = row[2] == null ? "" : row[2].ToString();

                    CourseShows courses = new CourseShows(myLevel, myTeacher);
                    courses.CourseId = Convert.ToInt32(row[0].ToString().Trim());
                    courses.CourseName = row[1].ToString().Trim();
                    courses.Teacher.TeacherName = row[2].ToString().Trim();
                    courses.Level.LevelName = row[3].ToString().Trim();
                    courses.CourseStatus = Convert.ToBoolean(row[4].ToString().Trim());
                    courseshowlist.Add(courses);
                }
                return courseshowlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateCourse(Course course)
        {
            string sql = "Update Course set CourseName=@CourseName , TeacherId=@TeacherId ,LevelId=@LevelId , CourseStatus=@CourseStatus where CourseId=@CourseId  ";
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            try
            {
                sqlconn.Open();
                SqlParameter sp1 = new SqlParameter("CourseId", course.CourseId);
                SqlParameter sp2 = new SqlParameter("CourseName", course.CourseName);
                SqlParameter sp3 = new SqlParameter("TeacherId", course.TeacherId);
                SqlParameter sp4 = new SqlParameter("LevelId", course.LevelId);
                SqlParameter sp5 = new SqlParameter("CourseStatus", course.CourseStatus);
                cmd.Parameters.Add(sp1);
                cmd.Parameters.Add(sp2);
                cmd.Parameters.Add(sp3);
                cmd.Parameters.Add(sp4);
                cmd.Parameters.Add(sp5);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                cmd.ExecuteNonQuery();
            }
            finally
            {
                sqlconn.Close();
            }
        }

        public void DeleteCourse(string id)
        {

            string sql = "delete from Course where CourseId='" + id + "'";
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            try
            {
                sqlconn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public int ShowStatus(string id)
        {
            string sql = "select CourseStatus from Course where CourseId='" + id + "'";
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            try
            {
                sqlconn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                bool status;
                while (reader.Read())
                {
                    status = (Boolean)reader["CourseStatus"];
                    if (status)
                    {
                        return 1;
                    }
                    else
                    {
                        return 0;
                    }
                }
                reader.Close();
                return -1;

            }
            finally
            {

                sqlconn.Close();
            }
        }
        public void ChangeState(Course course)
        {
            string sql = "Update Course set CourseStatus='false' where CourseId=@CourseId";
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            try
            {
                sqlconn.Open();
                SqlParameter sp1 = new SqlParameter("CourseId", course.CourseId);
                cmd.Parameters.Add(sp1);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                cmd.ExecuteNonQuery();

            }
            finally
            {
                sqlconn.Close();
            }
        }
    }
}
